//-------------------------------------------------------------------
//文件名称：ESysUser.cs
//模块名称：ESysUser数据访问层
//功能说明：
//-----------------------------------------------------------------
//修改记录：
//修改人：Dawen
//修改时间：2019年8月22日
//-----------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ELearning.Common.Model;
using ELearning.Models;
using ELearning.Models.ViewModel.User;

namespace ELearning.DAL
{
    /// <summary>
    /// ESysUser数据访问层
    /// </summary>
	public partial class ESysUserRepository : BaseRepository<ESysUser>
    {

        public Page<SysUserModel> GetISVUserList(GetUserRequest request)
        {
            var where = string.Empty;
            if (!string.IsNullOrEmpty(request.UserType))
            {
                where = $"AND u.UserType = '{request.UserType}'";
            }
            if (!string.IsNullOrEmpty(request.UserName))
            {
                where = $"{where} AND u.UserName LIKE N'%{request.UserName}%'";
            }
            if (!string.IsNullOrEmpty(request.LoginName))
            {
                where = $"{where} AND u.LoginName LIKE N'%{request.LoginName}%'";
            }
            if (!string.IsNullOrEmpty(request.Unumber))
            {
                where = $"{where} AND u.Unumber LIKE N'%{request.Unumber}%'";
            }

            var startIndex = (request.Page - 1) * request.PageSize + 1;
            var endIndex = request.PageSize * request.Page;

            var sql = $@"
            SELECT
	            *
            FROM
	            (
		            SELECT 
			            ROW_NUMBER() OVER(ORDER BY u.CreateTime DESC)rn,
			            u.* ,
			            COUNT(1) OVER() Total
		            FROM 
			            dbo.ESysUser u
		            WHERE
			            u.IsDelete = 0 {where}
	            )a
            WHERE
	            a.rn BETWEEN {startIndex} AND {endIndex}";

            var items = nContext.Database.SqlQuery<SysUserModel>(sql).ToList();
            var page = new Page<SysUserModel>
            {
                TotalItems = items.FirstOrDefault()?.Total ?? 0,
                CurrentPage = request.Page,
                Items = items,
                ItemsPerPage = request.PageSize
            };
            return page;
        }

        /// <summary>
        /// 获取用户列表
        /// </summary>
        public Page<SysUserModel> GetAdminUserList(GetUserRequest request)
        {
            var sql = "exec NTP_Page @IndexField,@AllFields,@TablesAndWhere,@OrderFields,@PageSize,@PageIndex,@RecordCount out,@PageCount out";
            //SqlParameter[] paras =
            //{
            //    new SqlParameter("@IndexField","userid"),
            //    new SqlParameter("@AllFields","*"),
            //    new SqlParameter("@TablesAndWhere","esysuser  where 1=1"),
            //    new SqlParameter("@OrderFields","order by createtime desc"),
            //    new SqlParameter("@PageSize",10),
            //    new SqlParameter("@PageIndex",1),
            //    new SqlParameter("@RecordCount",System.Data.SqlDbType.Int),
            //    new SqlParameter("@PageCount",System.Data.SqlDbType.Int)
            //};
            //paras[6].Direction = ParameterDirection.Output;
            //paras[7].Direction = ParameterDirection.Output;
            //int PageCount = 0;
            //int RecordCount = 0;
            //var sql = "exec NTP_Page @UserName,@LoginName,@MobilePhone,@UserType,@OrgCode,@Page,@PageSize,@OrderBy,@SortDic";
            //var sd = "desc";
            //if (request.IsAsc)
            //    sd = "asc";
            //var paras = new List<SqlParameter>
            //{
            //    new SqlParameter("@UserName",request.UserName),
            //    new SqlParameter("@LoginName",request.LoginName),
            //    new SqlParameter("@MobilePhone",request.MobilePhone),
            //    new SqlParameter("@UserType",request.UserType),
            //    new SqlParameter("@OrgCode",request.OrgCode),
            //    new SqlParameter("@Page",request.Page),
            //    new SqlParameter("@PageSize",request.PageSize),
            //    new SqlParameter("@OrderBy",request.OrderBy),
            //    new SqlParameter("@SortDic",sd),
            //};

            SqlParameter[] parameters = GetParam("*", "esysuser  where 1=1", "userId", "order by createtime desc", 1, 10);

            var items = nContext.Database.SqlQuery<SysUserModel>(sql, parameters).ToList();
            string RecordCount = parameters[6].Value.ToString();
            int PageCount = (int)parameters[7].Value;

            Page<SysUserModel> page = new Page<SysUserModel>
            {
                TotalItems = long.Parse(RecordCount),
                CurrentPage = request.Page,
                Items = items,
                ItemsPerPage = request.PageSize
            };
            return page;
        }
        /// <summary>
        /// 取得参数
        /// </summary>
        /// <param name="SqlAllFields"></param>
        /// <param name="SqlTablesAndWhere"></param>
        /// <param name="IndexField"></param>
        /// <param name="OrderFields"></param>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        static SqlParameter[] GetParam(string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize)
        {
            SqlParameter[] param = new SqlParameter[] {
                new SqlParameter{ParameterName = "@IndexField", Value=IndexField },
                new SqlParameter{ParameterName = "@AllFields", Value=SqlAllFields },
                new SqlParameter{ParameterName = "@TablesAndWhere", Value=SqlTablesAndWhere },
                new SqlParameter{ParameterName = "@OrderFields", Value=OrderFields },
                new SqlParameter{ParameterName = "@PageSize", Value=PageSize },
                new SqlParameter{ParameterName = "@PageIndex", Value=PageIndex },
                new SqlParameter{ParameterName = "@RecordCount",Value=0, Direction=ParameterDirection.Output },
                new SqlParameter{ParameterName = "@PageCount",Value=0, Direction=ParameterDirection.Output }
            };
            return param;
        }

        /// <summary>
        /// 根据消息id获取对应的接收消息用户
        /// </summary>
        /// <param name="messageId">消息id</param>
        public List<ESysUser> GetSendUserByMsgId(Guid messageId)
        {
            var sql = @" exec sp_getSendMsgUsers @messageId";
            var paras = new SqlParameter[]
            {
                new SqlParameter("@messageId",messageId)
            };

            return nContext.Database.SqlQuery<ESysUser>(sql, paras).ToList();
        }
    }
}


